set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_route_network_effect_base_dt
select
     base.in_from_code
    ,base.in_from_name
    ,base.in_collect_code
    ,base.in_collect_name                   
    ,base.in_to_code
    ,base.in_to_name
    ,base.in_from_area_code
    ,base.in_from_area_desc 
    ,base.in_from_city_code
    ,base.in_from_city_desc 
    ,base.in_from_provider_code
    ,base.in_from_provider_desc
    ,base.in_from_financial_center_code      
    ,base.in_from_financial_center_desc      
    ,base.in_from_regional_code              
    ,base.in_from_regional_desc    
    ,base.out_to_code                       
    ,base.out_to_name
    ,base.out_collect_code
    ,base.out_collect_name                   
    ,base.out_from_code
    ,base.out_from_name
    ,base.out_to_area_code
    ,base.out_to_area_desc 
    ,base.out_to_city_code
    ,base.out_to_city_desc 
    ,base.out_to_provider_code
    ,base.out_to_provider_desc
    ,base.out_to_financial_center_code      
    ,base.out_to_financial_center_desc      
    ,base.out_to_regional_code              
    ,base.out_to_regional_desc         
    ,base.route_flow                       
    ,base.is_main_route                    
    ,base.whole_route                      
    ,base.is_main_city
    ,base.total_days_use                           --全程时效(X日达)
    ,base.total_time_use                           --全程耗时(小时)
    ,base.total_days_t                             --全程T+时效(天)
    ,base.latest_warehousing_time_quantum          --揽收时间段
    ,cn_effe.platform_code                  as platform_code_cn
    ,cn_effe.platform                       as platform_cn
    ,cn_effe.times                          as effective_times_cn
    ,zt_effe.platform_code                  as platform_code_zt
    ,zt_effe.platform                       as platform_zt
    ,zt_effe.times                          as effective_times_zt
    ,sf_effe.platform_code                  as platform_code_sf
    ,sf_effe.platform                       as platform_sf
    ,sf_effe.times                          as effective_times_sf
    ,yt_effe.platform_code                  as platform_code_yt
    ,yt_effe.platform                       as platform_yt
    ,yt_effe.times                          as effective_times_yt
    ,yd_effe.platform_code                  as platform_code_yd
    ,yd_effe.platform                       as platform_yd
    ,yd_effe.times                          as effective_times_yd
    ,thd_effe.platform_code                 as platform_code_thd
    ,thd_effe.platform                      as platform_thd
    ,thd_effe.times                         as effective_times_thd
    ,zjs_effe.platform_code                 as platform_code_zjs
    ,zjs_effe.platform                      as platform_zjs
    ,zjs_effe.times                         as effective_times_zjs
    ,route.cn_cp_line_cnt                          --持平菜鸟班次数
    ,route.zt_cp_line_cnt                          --持平中通班次数
    ,route.sf_cp_line_cnt                          --持平顺丰班次数
    ,route.yt_cp_line_cnt                          --持平圆通班次数
    ,route.yd_cp_line_cnt                          --持平韵达班次数
    ,route.thd_cp_line_cnt                         --持平桃花岛班次数
    ,route.zjs_cp_line_cnt                         --持平紫金山班次数
    ,route.cn_my_line_cnt                          --慢于菜鸟班次数
    ,route.zt_my_line_cnt                          --慢于中通班次数
    ,route.sf_my_line_cnt                          --慢于顺丰班次数
    ,route.yt_my_line_cnt                          --慢于圆通班次数
    ,route.yd_my_line_cnt                          --慢于韵达班次数
    ,route.thd_my_line_cnt                         --慢于桃花岛班次数
    ,route.zjs_my_line_cnt                         --慢于紫金山班次数
    ,route.all_line_cnt                            --总班次数
    ,case when route.all_cnt is null then round(route.cn_cp_line_cnt  / route.all_line_cnt * base.cnt,2) else round(route.cn_cp_cnt  / route.all_cnt * base.cnt,2) end as cn_cp_cnt  --持平菜鸟件量
    ,case when route.all_cnt is null then round(route.zt_cp_line_cnt  / route.all_line_cnt * base.cnt,2) else round(route.zt_cp_cnt  / route.all_cnt * base.cnt,2) end as zt_cp_cnt  --持平中通件量
    ,case when route.all_cnt is null then round(route.sf_cp_line_cnt  / route.all_line_cnt * base.cnt,2) else round(route.sf_cp_cnt  / route.all_cnt * base.cnt,2) end as sf_cp_cnt  --持平顺丰件量
    ,case when route.all_cnt is null then round(route.yt_cp_line_cnt  / route.all_line_cnt * base.cnt,2) else round(route.yt_cp_cnt  / route.all_cnt * base.cnt,2) end as yt_cp_cnt  --持平圆通件量
    ,case when route.all_cnt is null then round(route.yd_cp_line_cnt  / route.all_line_cnt * base.cnt,2) else round(route.yd_cp_cnt  / route.all_cnt * base.cnt,2) end as yd_cp_cnt  --持平韵达件量
    ,case when route.all_cnt is null then round(route.thd_cp_line_cnt / route.all_line_cnt * base.cnt,2) else round(route.thd_cp_cnt / route.all_cnt * base.cnt,2) end as thd_cp_cnt --持平桃花岛件量
    ,case when route.all_cnt is null then round(route.zjs_cp_line_cnt / route.all_line_cnt * base.cnt,2) else round(route.zjs_cp_cnt / route.all_cnt * base.cnt,2) end as zjs_cp_cnt --持平紫金山件量
    ,case when route.all_cnt is null then round((route.cn_my_line_cnt  / route.all_line_cnt * base.cnt),2) else round(base.cnt - (route.cn_cp_cnt  / route.all_cnt * base.cnt),2) end as cn_my_cnt  --慢于菜鸟件量
    ,case when route.all_cnt is null then round((route.zt_my_line_cnt  / route.all_line_cnt * base.cnt),2) else round(base.cnt - (route.zt_cp_cnt  / route.all_cnt * base.cnt),2) end as zt_my_cnt  --慢于中通件量
    ,case when route.all_cnt is null then round((route.sf_my_line_cnt  / route.all_line_cnt * base.cnt),2) else round(base.cnt - (route.sf_cp_cnt  / route.all_cnt * base.cnt),2) end as sf_my_cnt  --慢于顺丰件量
    ,case when route.all_cnt is null then round((route.yt_my_line_cnt  / route.all_line_cnt * base.cnt),2) else round(base.cnt - (route.yt_cp_cnt  / route.all_cnt * base.cnt),2) end as yt_my_cnt  --慢于圆通件量
    ,case when route.all_cnt is null then round((route.yd_my_line_cnt  / route.all_line_cnt * base.cnt),2) else round(base.cnt - (route.yd_cp_cnt  / route.all_cnt * base.cnt),2) end as yd_my_cnt  --慢于韵达件量
    ,case when route.all_cnt is null then round((route.thd_my_line_cnt / route.all_line_cnt * base.cnt),2) else round(base.cnt - (route.thd_cp_cnt / route.all_cnt * base.cnt),2) end as thd_my_cnt --慢于桃花岛件量
    ,case when route.all_cnt is null then round((route.zjs_my_line_cnt / route.all_line_cnt * base.cnt),2) else round(base.cnt - (route.zjs_cp_cnt / route.all_cnt * base.cnt),2) end as zjs_my_cnt --慢于紫金山件量
    ,base.cnt as all_cnt    --总件量
    ,base.dt
from (
    select
         in_from_code
        ,in_from_name
        ,in_collect_code
        ,in_collect_name
        ,in_to_code
        ,in_to_name
        ,in_from_area_code
        ,in_from_area_desc 
        ,in_from_city_code
        ,in_from_city_desc 
        ,in_from_provider_code
        ,in_from_provider_desc
        ,in_from_financial_center_code      
        ,in_from_financial_center_desc      
        ,in_from_regional_code              
        ,in_from_regional_desc    
        ,out_to_code                       
        ,out_to_name
        ,out_collect_code
        ,out_collect_name                   
        ,out_from_code
        ,out_from_name
        ,out_to_area_code
        ,out_to_area_desc 
        ,out_to_city_code
        ,out_to_city_desc 
        ,out_to_provider_code
        ,out_to_provider_desc
        ,out_to_financial_center_code      
        ,out_to_financial_center_desc      
        ,out_to_regional_code              
        ,out_to_regional_desc         
        ,route_flow                       
        ,is_main_route                    
        ,whole_route                      
        ,is_main_city
        ,latest_warehousing_time_quantum
        ,total_days_use                          
        ,total_time_use                          
        ,total_days_t          
        ,count(1) as cnt
        ,dt
    from (
        select
             last.in_from_code                      --始发网点编码                           
            ,last.in_from_name                      --始发网点名称                          
            ,last.in_collect_code                   --始发集散点点编码                           
            ,last.in_collect_name                   --始发集散点点名称                           
            ,last.in_to_code                        --始发中心编码                           
            ,last.in_to_name                        --始发中心名称                           
            ,last.in_from_area_code                 --始发区县编码                          
            ,last.in_from_area_desc                 --始发区县名称                           
            ,last.in_from_city_code                 --始发城市编码                           
            ,last.in_from_city_desc                 --始发城市名称                           
            ,last.in_from_provider_code             --始发省份编码                           
            ,last.in_from_provider_desc             --始发省份名称                          
            ,last.in_from_financial_center_code     --始发代理区编码                            
            ,last.in_from_financial_center_desc     --始发代理区名称                            
            ,last.in_from_regional_code             --始发大区编码                            
            ,last.in_from_regional_desc             --始发大区名称                            
            ,last.out_to_code                       --目的网点编码                                        
            ,last.out_to_name                       --目的网点名称                                        
            ,last.out_collect_code                  --目的集散点点编码                                      
            ,last.out_collect_name                  --目的集散点点名称                                      
            ,last.out_from_code                     --目的中心编码                                        
            ,last.out_from_name                     --目的中心名称                                        
            ,last.out_to_area_code                  --目的区县编码                                        
            ,last.out_to_area_desc                  --目的区县名称                                        
            ,last.out_to_city_code                  --目的城市编码                                        
            ,last.out_to_city_desc                  --目的城市名称                                        
            ,last.out_to_provider_code              --目的省份编码                                        
            ,last.out_to_provider_desc              --目的省份名称                                        
            ,last.out_to_financial_center_code      --目的代理区编码                                       
            ,last.out_to_financial_center_desc      --目的代理区名称                                       
            ,last.out_to_regional_code              --目的大区编码                                        
            ,last.out_to_regional_desc              --目的大区名称                                        
            ,last.route_flow                        --始发地-目的地流向        
            ,last.is_main_route                     --路由类型                  
            ,last.whole_route                       --路由环节  
            ,last.total_days_use                    --全程时效(X日达)
            ,last.total_time_use                    --全程耗时(小时)
            ,last.total_days_t                      --全程T+时效(天)
            ,last.latest_warehousing_time_quantum as latest_warehousing_time_quantum   --揽收时间段
            ,null as is_main_city --当始发区县-目的区县都为主城区时,则该条路由为城市-城市路由
            ,way.waybill_no --运单号
            ,way.collect_time 
            ,date_add(last.dt,1) as dt 
        from (
            select
                 lastest.*
                ,substr(latest_warehousing_time_quantum,1,5)  as start_taking_time --揽收开始时间
                ,substr(latest_warehousing_time_quantum,-5,5) as end_taking_time --揽收结束时间
                ,row_number() over(partition by dt,in_from_code, out_to_code order by substr(latest_warehousing_time_quantum,1,5) )     as rk_asc
                ,row_number() over(partition by dt,in_from_code, out_to_code order by substr(latest_warehousing_time_quantum,1,5) desc) as rk_desc
            from jms_dm.dm_route_lastest_base_dt lastest --兜底路由
             where lastest.dt = date_sub('{{ execution_date | cst_ds }}',1)
        ) last
        left join (
            select
                 way.waybill_no             as waybill_no            --运单号
                ,way.delivery_time          as delivery_time         --寄件时间
                ,way.pick_network_code      as pick_network_code     --寄件网点code
                ,way.pick_network_name      as pick_network_name     --寄件网点名称
                ,dim_start.area_id          as pick_area_code        --寄件区县code
                ,dim_start.area_desc        as pick_area_name        --寄件区县名称
                ,dim_start.city_id          as pick_city_code        --寄件城市code
                ,dim_start.city_desc        as pick_city_name        --寄件城市名称
                ,way.dispatch_network_code  as dispatch_network_code --派件网点code
                ,way.dispatch_network_name  as dispatch_network_name --派件网点名称
                ,dim_end.area_id            as dispatch_area_code    --寄件区县code
                ,dim_end.area_desc          as dispatch_area_name    --寄件区县名称
                ,dim_end.city_id            as dispatch_city_code    --寄件城市code
                ,dim_end.city_desc          as dispatch_city_name    --寄件城市名称
                ,way.collect_time           as collect_time          --揽件时间
            from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt way
            left join jms_dim.dim_network_whole_massage dim_start on dim_start.code = way.pick_network_code
            left join jms_dim.dim_network_whole_massage dim_end   on dim_end.code   = way.dispatch_network_code
            where way.dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
            and to_date(way.collect_time) = '{{ execution_date | cst_ds }}'
            and is_void <> 1 
        ) way on way.pick_network_code = last.in_from_code
             and way.dispatch_network_code = last.out_to_code
             and substr(way.collect_time,12,5) between concat(if(last.rk_asc = 1, '00:00' , last.start_taking_time),':00') 
             and concat(if(last.rk_desc = 1, '23:59' , last.end_taking_time),':59') --在揽收时间段之内  
    ) a group by in_from_code
                ,in_from_name
                ,in_collect_code
                ,in_collect_name
                ,in_to_code
                ,in_to_name
                ,in_from_area_code
                ,in_from_area_desc 
                ,in_from_city_code
                ,in_from_city_desc 
                ,in_from_provider_code
                ,in_from_provider_desc
                ,in_from_financial_center_code      
                ,in_from_financial_center_desc      
                ,in_from_regional_code              
                ,in_from_regional_desc    
                ,out_to_code                       
                ,out_to_name
                ,out_collect_code
                ,out_collect_name                   
                ,out_from_code
                ,out_from_name
                ,out_to_area_code
                ,out_to_area_desc 
                ,out_to_city_code
                ,out_to_city_desc 
                ,out_to_provider_code
                ,out_to_provider_desc
                ,out_to_financial_center_code      
                ,out_to_financial_center_desc      
                ,out_to_regional_code              
                ,out_to_regional_desc         
                ,route_flow                       
                ,is_main_route                    
                ,whole_route                      
                ,is_main_city
                ,latest_warehousing_time_quantum
                ,total_days_use                          
                ,total_time_use                          
                ,total_days_t      
                ,dt                  
) base
left join (
    select 
         in_from_code
        ,out_to_code
        ,latest_warehousing_time_quantum     
        ,max(platform_code_cn   ) as platform_code_cn    --菜鸟平台编码
        ,max(platform_cn        ) as platform_cn         --菜鸟平台名称
        ,max(effective_times_cn ) as effective_times_cn  --菜鸟平台时效
        ,max(platform_code_zt   ) as platform_code_zt    --中通平台编码
        ,max(platform_zt        ) as platform_zt         --中通平台名称
        ,max(effective_times_zt ) as effective_times_zt  --中通平台时效
        ,max(platform_code_sf   ) as platform_code_sf    --顺丰平台编码
        ,max(platform_sf        ) as platform_sf         --顺丰平台名称
        ,max(effective_times_sf ) as effective_times_sf  --顺丰平台时效
        ,max(platform_code_yt   ) as platform_code_yt    --圆通平台编码
        ,max(platform_yt        ) as platform_yt         --圆通平台名称
        ,max(effective_times_yt ) as effective_times_yt  --圆通平台时效
        ,max(platform_code_yd   ) as platform_code_yd    --韵达平台编码
        ,max(platform_yd        ) as platform_yd         --韵达平台名称
        ,max(effective_times_yd ) as effective_times_yd  --韵达平台时效
        ,max(platform_code_thd  ) as platform_code_thd   --桃花岛平台编码
        ,max(platform_thd       ) as platform_thd        --桃花岛平台名称
        ,max(effective_times_thd) as effective_times_thd --桃花岛平台时效
        ,max(platform_code_zjs  ) as platform_code_zjs   --紫金山平台编码
        ,max(platform_zjs       ) as platform_zjs        --紫金山平台名称
        ,max(effective_times_zjs) as effective_times_zjs --紫金山平台时效
        ,sum(cn_cp_line_cnt ) as cn_cp_line_cnt  --持平菜鸟班次数
        ,sum(zt_cp_line_cnt ) as zt_cp_line_cnt  --持平中通班次数
        ,sum(sf_cp_line_cnt ) as sf_cp_line_cnt  --持平顺丰班次数
        ,sum(yt_cp_line_cnt ) as yt_cp_line_cnt  --持平圆通班次数
        ,sum(yd_cp_line_cnt ) as yd_cp_line_cnt  --持平韵达班次数
        ,sum(thd_cp_line_cnt) as thd_cp_line_cnt --持平桃花岛班次数
        ,sum(zjs_cp_line_cnt) as zjs_cp_line_cnt --持平紫金山班次数
        ,sum(cn_my_line_cnt ) as cn_my_line_cnt  --慢于菜鸟班次数
        ,sum(zt_my_line_cnt ) as zt_my_line_cnt  --慢于中通班次数
        ,sum(sf_my_line_cnt ) as sf_my_line_cnt  --慢于顺丰班次数
        ,sum(yt_my_line_cnt ) as yt_my_line_cnt  --慢于圆通班次数
        ,sum(yd_my_line_cnt ) as yd_my_line_cnt  --慢于韵达班次数
        ,sum(thd_my_line_cnt) as thd_my_line_cnt --慢于桃花岛班次数
        ,sum(zjs_my_line_cnt) as zjs_my_line_cnt --慢于紫金山班次数
        ,sum(all_line_cnt   ) as all_line_cnt    --总班次数
        ,sum(cn_cp_cnt ) as cn_cp_cnt  --持平菜鸟件量     
        ,sum(zt_cp_cnt ) as zt_cp_cnt  --持平中通件量     
        ,sum(sf_cp_cnt ) as sf_cp_cnt  --持平顺丰件量     
        ,sum(yt_cp_cnt ) as yt_cp_cnt  --持平圆通件量     
        ,sum(yd_cp_cnt ) as yd_cp_cnt  --持平韵达件量     
        ,sum(thd_cp_cnt) as thd_cp_cnt --持平桃花岛件量    
        ,sum(zjs_cp_cnt) as zjs_cp_cnt --持平紫金山件量    
        ,sum(cn_my_cnt ) as cn_my_cnt  --慢于菜鸟件量    
        ,sum(zt_my_cnt ) as zt_my_cnt  --慢于中通件量    
        ,sum(sf_my_cnt ) as sf_my_cnt  --慢于顺丰件量    
        ,sum(yt_my_cnt ) as yt_my_cnt  --慢于圆通件量    
        ,sum(yd_my_cnt ) as yd_my_cnt  --慢于韵达件量    
        ,sum(thd_my_cnt) as thd_my_cnt --慢于桃花岛件量   
        ,sum(zjs_my_cnt) as zjs_my_cnt --慢于紫金山件量   
        ,sum(all_cnt   ) as all_cnt --总件量
    from (
         select 
              in_from_code
             ,out_to_code
             ,latest_warehousing_time_quantum     
             ,platform_code_cn     --菜鸟平台编码
             ,platform_cn          --菜鸟平台名称
             ,effective_times_cn   --菜鸟平台时效
             ,platform_code_zt     --中通平台编码
             ,platform_zt          --中通平台名称
             ,effective_times_zt   --中通平台时效
             ,platform_code_sf     --顺丰平台编码
             ,platform_sf          --顺丰平台名称
             ,effective_times_sf   --顺丰平台时效
             ,platform_code_yt     --圆通平台编码
             ,platform_yt          --圆通平台名称
             ,effective_times_yt   --圆通平台时效
             ,platform_code_yd     --韵达平台编码
             ,platform_yd          --韵达平台名称
             ,effective_times_yd   --韵达平台时效
             ,platform_code_thd    --桃花岛平台编码
             ,platform_thd         --桃花岛平台名称
             ,effective_times_thd  --桃花岛平台时效
             ,platform_code_zjs    --紫金山平台编码
             ,platform_zjs         --紫金山平台名称
             ,effective_times_zjs  --紫金山平台时效
             ,case when if_city = 1 and cn_cp_line_cnt  > 0 then all_line_cnt else cn_cp_line_cnt  end as cn_cp_line_cnt  --持平菜鸟班次数
             ,case when if_city = 1 and zt_cp_line_cnt  > 0 then all_line_cnt else zt_cp_line_cnt  end as zt_cp_line_cnt  --持平中通班次数
             ,case when if_city = 1 and sf_cp_line_cnt  > 0 then all_line_cnt else sf_cp_line_cnt  end as sf_cp_line_cnt  --持平顺丰班次数
             ,case when if_city = 1 and yt_cp_line_cnt  > 0 then all_line_cnt else yt_cp_line_cnt  end as yt_cp_line_cnt  --持平圆通班次数
             ,case when if_city = 1 and yd_cp_line_cnt  > 0 then all_line_cnt else yd_cp_line_cnt  end as yd_cp_line_cnt  --持平韵达班次数
             ,case when if_city = 1 and thd_cp_line_cnt > 0 then all_line_cnt else thd_cp_line_cnt end as thd_cp_line_cnt --持平桃花岛班次数
             ,case when if_city = 1 and zjs_cp_line_cnt > 0 then all_line_cnt else zjs_cp_line_cnt end as zjs_cp_line_cnt --持平紫金山班次数
             ,case when if_city = 1 and cn_cp_line_cnt  > 0 then 0 else cn_my_line_cnt  end as cn_my_line_cnt  --慢于菜鸟班次数
             ,case when if_city = 1 and zt_cp_line_cnt  > 0 then 0 else zt_my_line_cnt  end as zt_my_line_cnt  --慢于中通班次数
             ,case when if_city = 1 and sf_cp_line_cnt  > 0 then 0 else sf_my_line_cnt  end as sf_my_line_cnt  --慢于顺丰班次数
             ,case when if_city = 1 and yt_cp_line_cnt  > 0 then 0 else yt_my_line_cnt  end as yt_my_line_cnt  --慢于圆通班次数
             ,case when if_city = 1 and yd_cp_line_cnt  > 0 then 0 else yd_my_line_cnt  end as yd_my_line_cnt  --慢于韵达班次数
             ,case when if_city = 1 and thd_cp_line_cnt > 0 then 0 else thd_my_line_cnt end as thd_my_line_cnt --慢于桃花岛班次数
             ,case when if_city = 1 and zjs_cp_line_cnt > 0 then 0 else zjs_my_line_cnt end as zjs_my_line_cnt --慢于紫金山班次数
             ,all_line_cnt as all_line_cnt    --总班次数
             ,case when if_city = 1 and cn_cp_line_cnt  > 0 then all_cnt else cn_cp_cnt  end as cn_cp_cnt  --持平菜鸟件量   
             ,case when if_city = 1 and zt_cp_line_cnt  > 0 then all_cnt else zt_cp_cnt  end as zt_cp_cnt  --持平中通件量   
             ,case when if_city = 1 and sf_cp_line_cnt  > 0 then all_cnt else sf_cp_cnt  end as sf_cp_cnt  --持平顺丰件量   
             ,case when if_city = 1 and yt_cp_line_cnt  > 0 then all_cnt else yt_cp_cnt  end as yt_cp_cnt  --持平圆通件量   
             ,case when if_city = 1 and yd_cp_line_cnt  > 0 then all_cnt else yd_cp_cnt  end as yd_cp_cnt  --持平韵达件量   
             ,case when if_city = 1 and thd_cp_line_cnt > 0 then all_cnt else thd_cp_cnt end as thd_cp_cnt --持平桃花岛件量 
             ,case when if_city = 1 and zjs_cp_line_cnt > 0 then all_cnt else zjs_cp_cnt end as zjs_cp_cnt --持平紫金山件量 
             ,case when if_city = 1 and cn_cp_line_cnt  > 0 then 0 else cn_my_cnt  end as cn_my_cnt  --慢于菜鸟件量         
             ,case when if_city = 1 and zt_cp_line_cnt  > 0 then 0 else zt_my_cnt  end as zt_my_cnt  --慢于中通件量         
             ,case when if_city = 1 and sf_cp_line_cnt  > 0 then 0 else sf_my_cnt  end as sf_my_cnt  --慢于顺丰件量         
             ,case when if_city = 1 and yt_cp_line_cnt  > 0 then 0 else yt_my_cnt  end as yt_my_cnt  --慢于圆通件量         
             ,case when if_city = 1 and yd_cp_line_cnt  > 0 then 0 else yd_my_cnt  end as yd_my_cnt  --慢于韵达件量         
             ,case when if_city = 1 and thd_cp_line_cnt > 0 then 0 else thd_my_cnt end as thd_my_cnt --慢于桃花岛件量       
             ,case when if_city = 1 and zjs_cp_line_cnt > 0 then 0 else zjs_my_cnt end as zjs_my_cnt --慢于紫金山件量     
             ,all_cnt as all_cnt --总件量  
             ,if_city
         from (
             select      
                  in_from_code
                 ,out_to_code
                 ,latest_warehousing_time_quantum
                 ,branch_shifts
                 ,max(if_city            ) as if_city              --是否包含一条同城路由           
                 ,max(platform_code_cn   ) as platform_code_cn     --菜鸟平台编码
                 ,max(platform_cn        ) as platform_cn          --菜鸟平台名称
                 ,max(effective_times_cn ) as effective_times_cn   --菜鸟平台时效
                 ,max(platform_code_zt   ) as platform_code_zt     --中通平台编码
                 ,max(platform_zt        ) as platform_zt          --中通平台名称
                 ,max(effective_times_zt ) as effective_times_zt   --中通平台时效
                 ,max(platform_code_sf   ) as platform_code_sf     --顺丰平台编码
                 ,max(platform_sf        ) as platform_sf          --顺丰平台名称
                 ,max(effective_times_sf ) as effective_times_sf   --顺丰平台时效
                 ,max(platform_code_yt   ) as platform_code_yt     --圆通平台编码
                 ,max(platform_yt        ) as platform_yt          --圆通平台名称
                 ,max(effective_times_yt ) as effective_times_yt   --圆通平台时效
                 ,max(platform_code_yd   ) as platform_code_yd     --韵达平台编码
                 ,max(platform_yd        ) as platform_yd          --韵达平台名称
                 ,max(effective_times_yd ) as effective_times_yd   --韵达平台时效
                 ,max(platform_code_thd  ) as platform_code_thd    --桃花岛平台编码
                 ,max(platform_thd       ) as platform_thd         --桃花岛平台名称
                 ,max(effective_times_thd) as effective_times_thd  --桃花岛平台时效
                 ,max(platform_code_zjs  ) as platform_code_zjs    --紫金山平台编码
                 ,max(platform_zjs       ) as platform_zjs         --紫金山平台名称
                 ,max(effective_times_zjs) as effective_times_zjs  --紫金山平台时效
                 ,sum(if_cn_cp ) as cn_cp_line_cnt  --持平菜鸟班次数
                 ,sum(if_zt_cp ) as zt_cp_line_cnt  --持平中通班次数
                 ,sum(if_sf_cp ) as sf_cp_line_cnt  --持平顺丰班次数
                 ,sum(if_yt_cp ) as yt_cp_line_cnt  --持平圆通班次数
                 ,sum(if_yd_cp ) as yd_cp_line_cnt  --持平韵达班次数
                 ,sum(if_thd_cp) as thd_cp_line_cnt --持平桃花岛班次数
                 ,sum(if_zjs_cp) as zjs_cp_line_cnt --持平紫金山班次数
                 ,sum(if_cn_my ) as cn_my_line_cnt  --慢于菜鸟班次数
                 ,sum(if_zt_my ) as zt_my_line_cnt  --慢于中通班次数
                 ,sum(if_sf_my ) as sf_my_line_cnt  --慢于顺丰班次数
                 ,sum(if_yt_my ) as yt_my_line_cnt  --慢于圆通班次数
                 ,sum(if_yd_my ) as yd_my_line_cnt  --慢于韵达班次数
                 ,sum(if_thd_my) as thd_my_line_cnt --慢于桃花岛班次数
                 ,sum(if_zjs_my) as zjs_my_line_cnt --慢于紫金山班次数
                 ,count(1)       as all_line_cnt    --总班次数
                 ,sum(case when if_cn_cp  = 1 then hz_cnt else 0 end) as cn_cp_cnt  --持平菜鸟件量     
                 ,sum(case when if_zt_cp  = 1 then hz_cnt else 0 end) as zt_cp_cnt  --持平中通件量     
                 ,sum(case when if_sf_cp  = 1 then hz_cnt else 0 end) as sf_cp_cnt  --持平顺丰件量     
                 ,sum(case when if_yt_cp  = 1 then hz_cnt else 0 end) as yt_cp_cnt  --持平圆通件量     
                 ,sum(case when if_yd_cp  = 1 then hz_cnt else 0 end) as yd_cp_cnt  --持平韵达件量     
                 ,sum(case when if_thd_cp = 1 then hz_cnt else 0 end) as thd_cp_cnt --持平桃花岛件量    
                 ,sum(case when if_zjs_cp = 1 then hz_cnt else 0 end) as zjs_cp_cnt --持平紫金山件量    
                 ,sum(case when if_cn_my  = 1 then hz_cnt else 0 end) as cn_my_cnt  --慢于菜鸟件量    
                 ,sum(case when if_zt_my  = 1 then hz_cnt else 0 end) as zt_my_cnt  --慢于中通件量    
                 ,sum(case when if_sf_my  = 1 then hz_cnt else 0 end) as sf_my_cnt  --慢于顺丰件量    
                 ,sum(case when if_yt_my  = 1 then hz_cnt else 0 end) as yt_my_cnt  --慢于圆通件量    
                 ,sum(case when if_yd_my  = 1 then hz_cnt else 0 end) as yd_my_cnt  --慢于韵达件量    
                 ,sum(case when if_thd_my = 1 then hz_cnt else 0 end) as thd_my_cnt --慢于桃花岛件量   
                 ,sum(case when if_zjs_my = 1 then hz_cnt else 0 end) as zjs_my_cnt --慢于紫金山件量   
                 ,sum(hz_cnt)    as all_cnt --总件量
             from jms_dm.dm_route_whole_shift_base_dt
             where dt = '{{ execution_date | cst_ds }}'
             group by in_from_code
                     ,out_to_code
                     ,latest_warehousing_time_quantum
                     ,branch_shifts
         ) a 
    ) a group by in_from_code
                ,out_to_code
                ,latest_warehousing_time_quantum
) route on route.in_from_code = base.in_from_code
       and route.out_to_code = base.out_to_code
       and route.latest_warehousing_time_quantum = base.latest_warehousing_time_quantum --揽收时间段

left join(
     select *
     from jms_dim.dim_tab_rou_competitor_effective --同行时效配置表
     where platform_code = 1 --菜鸟
) cn_effe on cn_effe.sender_city_id = base.in_from_city_code
         and cn_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 2 --中通
) zt_effe on zt_effe.sender_city_id = base.in_from_city_code
         and zt_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 3 --顺丰
) sf_effe on sf_effe.sender_city_id = base.in_from_city_code
         and sf_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 4 --圆通
) yt_effe on yt_effe.sender_city_id = base.in_from_city_code
         and yt_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 5 --韵达
) yd_effe on yd_effe.sender_city_id = base.in_from_city_code
         and yd_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 6 --桃花岛
) thd_effe on thd_effe.sender_city_id = base.in_from_city_code
          and thd_effe.receiver_city_id = base.out_to_city_code
left join(
    select *
    from jms_dim.dim_tab_rou_competitor_effective
    where platform_code = 7 --紫金山
) zjs_effe on zjs_effe.sender_city_id = base.in_from_city_code
          and zjs_effe.receiver_city_id = base.out_to_city_code
distribute by pmod(hash(rand()),60);



